﻿create PROCEDURE Sync.SP_CommitteesURLs_ApplyChanges
@RawValues XML 
AS 
BEGIN
	 
		DECLARE @CommitteesURLs_Temp TABLE 
		(
			CommitteeURLID UniqueIdentifier,
			[CommitteeName] nvarchar(50),
				[CommitteeURL] nvarchar(250),
				[CommitteeID] uniqueidentifier,
			IsDeleted BIT, 
			LastUpdatedDate DATETIME
		)
		
	   INSERT INTO @CommitteesURLs_Temp 
	   SELECT	Tbl.Col.value('CommitteeURLID[1]','uniqueidentifier') CommitteeURLID,
                Tbl.Col.value('CommitteeName[1]', 'nvarchar(50)') [CommitteeName],
Tbl.Col.value('CommitteeURL[1]', 'nvarchar(250)') [CommitteeURL],
Tbl.Col.value('CommitteeID[1]', 'uniqueidentifier') [CommitteeID],
				Tbl.Col.value('IsDeleted[1]', 'bit') IsDeleted,
				Tbl.Col.value('LastUpdatedDate[1]','DateTime') LastUpdatedDate
	   FROM   @RawValues.nodes('/DocumentElement/CommitteesURLs') Tbl(Col)
----------------------------------------------------------------------------------------

-- Insert ------------------------------------------------------------------------------

	INSERT INTO [dbo].[CommitteesURLs] ([CommitteeURLID],[CommitteeName],[CommitteeURL],[CommitteeID])
	SELECT t.[CommitteeURLID],t.[CommitteeName],t.[CommitteeURL],t.[CommitteeID]
	FROM  @CommitteesURLs_Temp t
	WHERE t.IsDeleted = 0
    AND NOT EXISTS
    (
        SELECT 1 FROM sync.CommitteesURLs_Tracking s
        WHERE t.CommitteeURLID = s.CommitteeURLID
    )
	-- put conflicts here.
	
-- Update -------------------------------------------------------------------------------
     
	UPDATE [dbo].[CommitteesURLs]
	SET [CommitteesURLs].[CommitteeName] = t.[CommitteeName],[CommitteesURLs].[CommitteeURL] = t.[CommitteeURL],[CommitteesURLs].[CommitteeID] = t.[CommitteeID] 
	FROM [dbo].[CommitteesURLs] s JOIN @CommitteesURLs_Temp t 
    ON t.CommitteeURLID = s.CommitteeURLID
	WHERE	t.IsDeleted = 0 
	AND		t.LastUpdatedDate IS NOT NULL
	AND		t.LastUpdatedDate >= 
				 isnull((SELECT TOP 1 LastUpdatedDate 
				  FROM sync.CommitteesURLs_Tracking r
				  WHERE t.CommitteeURLID = r.CommitteeURLID)
                  ,t.LastUpdatedDate)
	-----------------------------------------------------------------------------------------

-- Delete -------------------------------------------------------------------------------

	DELETE [dbo].[CommitteesURLs]
    FROM @CommitteesURLs_Temp t JOIN [dbo].[CommitteesURLs] s
    ON t.CommitteeURLID = s.CommitteeURLID
	WHERE t.IsDeleted = 1

-----------------------------------------------------------------------------------------
	
END











